﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using BLL;
using BLL.教师操作BLL;
using Model;

namespace NCRE学生考试端V1._0.教师端操作类
{
    public partial class frmAddGroupStudent : Form
    {
        public frmAddGroupStudent()
        {
            InitializeComponent();
        }

        #region 窗体单一显示-李金颖-2018-12-8 11:49:06
        private static frmAddGroupStudent FrmAddGroupStudent = null;
        public static frmAddGroupStudent GetInstance()
        {
            if (FrmAddGroupStudent==null|| FrmAddGroupStudent.IsDisposed)
            {
                FrmAddGroupStudent = new frmAddGroupStudent();
            }
            return FrmAddGroupStudent;
        }
        #endregion
        private void lblBrowseFile_Click(object sender, EventArgs e)
        {            
            this.openFileDialog1.ShowDialog();
            txtFileLog.Text = System.IO.Path.GetFullPath(openFileDialog1.FileName);
            ExcelToDataview(txtFileLog.Text.Trim());
        }
        
        /// <summary>
        /// 本地Excel文件
        /// </summary>
        static DataSet ds = null;
        //连接字符串
        static string strConn = "";
        DataTable dt;
        StudentInfoEntity studentInfo = new StudentInfoEntity();

        #region 显示本地Excel文件
        /// <summary>
        /// 显示本地Excel文件
        /// </summary>
        /// <param name="filename">文件绝对路径</param>
        public void ExcelToDataview(string filename)
        {
            //根据路径打开一个Excel文件并将数据填充到DataSet中            
            string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0; HDR = YES; IMEX = 1'";
            try
            {
                ////HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("请选择正确的文件");
                return;
            }
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = "select 学号,学院ID,考场,姓名,专业,年级,性别,班级,验证码,是否在线 from [导入学生$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds);

            int count = 0;
            foreach (DataTable dt in ds.Tables)
            {
                count += dt.Rows.Count;
            }
            cRows = count;
            cPages = cRows % PageSize == 0 ? cRows / PageSize : cRows / PageSize + 1;
            PageCurrent = 1;
            toolStripLabel3.Text = Convert.ToString(cRows);
            LoadData();
            dataGridView1.AllowUserToAddRows = false;
            // 设定包括Header和所有单元格的列宽自动调整
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            // 设定包括Header和所有单元格的行高自动调整
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
        }
        #endregion

        #region 分页功能-吕德彪-2018
        DataTable table1 = new DataTable();
        //定义分页需要的变量
        int cRows = 0;
        int RowCurrent = 0;
        int cPages = 0;
        int PageCurrent = 0;
        const int PageSize = 11;
        DataSet dtTemp;

        private void LoadData()
        {
            if (PageCurrent == 1)
            {
                ts_PagePrevious.Enabled = false;
                bindingNavigatorMoveFirstPage.Enabled = false;
                bindingNavigatorMoveLastPage.Enabled = true;
            }
            else
            {
                ts_PagePrevious.Enabled = true;
            }
            if (cPages == PageCurrent)
            {
                ts_PageNext.Enabled = false;
                bindingNavigatorMoveFirstPage.Enabled = true;
                bindingNavigatorMoveLastPage.Enabled = false;
            }
            else
            {
                ts_PageNext.Enabled = true;
            }
            ts_PagePostion.Text = PageCurrent.ToString();
            ts_Pages.Text = cPages.ToString();
            int startRow = (PageCurrent - 1) * PageSize;
            int endRow = PageCurrent == cPages ? cRows : PageCurrent * PageSize;
            dtTemp = ds.Clone();           
                for (int i = startRow; i < endRow; i++)
                {
                    if (i >= (ds.Tables[0].Rows.Count))
                    {
                        MessageBox.Show("请添加数据！");
                        return;
                    }               
                    dtTemp.Tables[0].ImportRow(ds.Tables[0].Rows[i]);
                }
            dataGridView1.DataSource = dtTemp.Tables[0];
            dataGridView1.Rows.Remove(this.dataGridView1.Rows[0]);
        }

        private void bindingNavigator1_ItemClicked_1(object sender, ToolStripItemClickedEventArgs e)
        {
            if (e.ClickedItem.Text == "上一页")
            {
                ts_PageNext.Enabled = true;
                if (PageCurrent == 1)
                {
                    ts_PagePrevious.Enabled = false;
                    MessageBox.Show("已经是第一页了");
                    return;
                }
                PageCurrent--;                
                LoadData();
            }
            if (e.ClickedItem.Text == "下一页")
            {
                ts_PagePrevious.Enabled = true;
                if (PageCurrent == PageSize)
                {
                    ts_PageNext.Enabled = false;
                    MessageBox.Show("已经是最后一页了");                    
                    return;
                }
                PageCurrent++;           
                LoadData();
            }
        }

        private void bindingNavigatorMoveLastPage_Click(object sender, EventArgs e)
        {
            PageCurrent = cPages;
            bindingNavigatorMoveFirstPage.Enabled = true;
            bindingNavigatorMoveLastPage.Enabled = false;
            ts_PagePrevious.Enabled = true;
            ts_PageNext.Enabled = false;
            LoadData();
        }

        private void bindingNavigatorMoveFirstPage_Click(object sender, EventArgs e)
        {
            PageCurrent = 1;
            bindingNavigatorMoveFirstPage.Enabled = false;
            bindingNavigatorMoveLastPage.Enabled = true;
            ts_PagePrevious.Enabled = false;
            ts_PageNext.Enabled = true;
            LoadData();
        }

        private void ts_PagePostion_TextChanged_1(object sender, EventArgs e)
        {
            try
            {
                if (Convert.ToInt32(ts_PagePostion.Text) > 0 && Convert.ToInt32(ts_PagePostion.Text) <= cPages)
                {
                    PageCurrent = Convert.ToInt32(ts_PagePostion.Text);
                    LoadData();
                }
                else
                {
                    throw new Exception();
                }
            }
            catch (System.Exception ex)
            {
                PageCurrent = 1;
                LoadData();
            }
        }

        #endregion

        #region 上传事件
        private void btnUpload_Click(object sender, EventArgs e)
        {
            if(dataGridView1.Rows.Count > 0)
            {
                //将数据集中的数据转到dt表中
                DataTable dt = ds.Tables[0];
                AddStudentBLL addStudent = new AddStudentBLL();

                //验证学生重复的表
                DataTable table1 = new DataTable();
                //验证学生不存在的表
                DataTable table2 = new DataTable();

                #region  为table1和table2加入对应的列名
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    table1.Columns.Add(dt.Columns[j].ColumnName);//有重载的方法，可以加入列数据的类型
                }
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    table2.Columns.Add(dt.Columns[j].ColumnName);//有重载的方法，可以加入列数据的类型
                }
                #endregion

                //将dt表中重复和不重复的学生分别分到table1和table2中
                for (int i = 1; i < dt.Rows.Count; i++)
                {
                    studentInfo.studentID = Convert.ToString(dt.Rows[i][0]);
                    DataTable table = addStudent.SelectStudent(studentInfo);
                    if (table.Rows.Count > 0)
                    {
                        //声明一个行
                        DataRow drq = table1.NewRow();
                        drq.ItemArray = dt.Rows[i].ItemArray;
                        table1.Rows.Add(drq);
                    }
                    else
                    {
                        DataRow drq = table2.NewRow();
                        drq.ItemArray = dt.Rows[i].ItemArray;
                        table2.Rows.Add(drq);
                    }
                }
                //接收批量导入学生的返回值
                bool flag = addStudent.AddGroupStudent(table2);
                if (flag = true)
                {
                    MessageBox.Show("已成功将数据导入到数据库中");
                }
                //导入成功的条数
                lblCount.Text = Convert.ToString(table2.Rows.Count);

            }
            else
            {
                MessageBox.Show("请先选择浏览文件！");
                return;
            }
        }
        #endregion

        #region datagridview转换datatable
        public DataTable GetDgvToTable(DataGridView dgv)
        {
            DataTable dt1 = new DataTable();
            //列强制转换
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt1.Columns.Add(dc);
            }

            //循环行
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt1.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt1.Rows.Add(dr);
            }
            return dt1;
        }


        #endregion       
        
        #region  下载文件模版
        private void linklblModel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            #region  
            /// <summary>
            /// 导出模板列集合
            /// </summary>
            List<string> columnListOut = new List<string>()
            {
                "学号",
                "学院ID",
                "考场",
                "姓名",
                "专业",
                "年级",
                "性别",
                "班级",
                "验证码",
                "是否在线"
            };
            string FileName = "批量导入学生模板";
            string SheetName = "导入学生";
            #endregion
            string[] columnList = columnListOut.ToArray();
            string msg = string.Empty;
            DownloadTemplate.SaveExcelTemplate(columnList, FileName, SheetName, ref msg);
        }
        #endregion
       
    }
}
